package com.suxin.sqlite;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.TypeReference;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.junit.Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Optional;

/**
 * @author Tang
 * @classname SqlReader
 * @description [ https://github.com/xerial/sqlite-jdbc ]
 * @date 2022/3/17 14:07
 */
public class SqlReader {

    public List<Question> read() throws Exception {

        List<Question> questions = new ArrayList<>();

        Connection connection = null;
        Statement statement = null;
        try {
            connection = DriverManager.getConnection("jdbc:sqlite:G:/javaConfig/alibaba/seata-1.3.0/cloud-test/sample.db");
            System.out.println("Opened database successfully");

            statement = connection.createStatement();
            ResultSet resultSet = statement
                    .executeQuery("select * from electrical_exam");
            while (resultSet.next()) {
                Question question = new Question();
                String selection = resultSet.getString("SELECTION");

                System.out.println(resultSet.getInt("_id"));
                question.setType(resultSet.getInt("TYPE"));

                // 选择题
                if (question.getType() != 0) {
                    List<String> options = JSON.parseObject(resultSet.getString("SELECTION"), new TypeReference<List<String>>() {
                    });
                    if (options.size() >= 1) Optional.ofNullable(options.get(0)).ifPresent(question::setOptionA);
                    if (options.size() >= 2) Optional.ofNullable(options.get(1)).ifPresent(question::setOptionB);
                    if (options.size() >= 3) Optional.ofNullable(options.get(2)).ifPresent(question::setOptionC);
                    if (options.size() >= 4) Optional.ofNullable(options.get(3)).ifPresent(question::setOptionD);
                }
                question.setId(null);
                question.setTitle(resultSet.getString("TITLE"));
                question.setCorrectOptions(resultSet.getString("ANSWER"));

                questions.add(question);
            }

        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            statement.close();
            connection.close();
        }
        return questions;
    }

    @Data
    @NoArgsConstructor
    static class Question {

        private Integer id;

        private String title;

        private String optionA;

        private String optionB;

        private String optionC;

        private String optionD;

        private String correctOptions;

        private Integer type;
    }


    @Test
    public void writer() {

    }

}